package com.ruoyi.tongji.run;

import com.ruoyi.tongji.run.manager.ManagerFactory;
import com.ruoyi.tongji.utils.DateUtil;
import com.ruoyi.tongji.utils.DateUtils;
import com.ruoyi.tongji.utils.JxlsUtils;
import com.ruoyi.tongji.utils.PlatformException;
import org.apache.commons.lang3.StringUtils;
import org.beetl.sql.core.*;
import org.beetl.sql.core.db.DBStyle;
import org.beetl.sql.core.db.MySqlStyle;
import org.beetl.sql.ext.DebugInterceptor;

import java.io.File;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.util.*;

/**
 * 职工任务数统计
 */
public class 车主二次采浆统计 {

    public static Date dateRangeMonth = DateUtil.getDate("2024-05-01");
    public static String renyuanDateRangeStart = "202404010000000";
    public static String renyuanDateRangeEnd =   "2024043012359999";
    public static String renyuanDateRangeNextEnd =   "202405312359999";

    public static String root = "E:\\文档\\2024\\每月统计\\" + DateUtils.getMonth(dateRangeMonth) + "月\\";

    public static void main(String[] args) {
        SQLManager jiangzhanSqlManager = ManagerFactory.jiangzhanSqlManager();

        List<HashMap> limkmanList = jiangzhanSqlManager.execute(new SQLReady("select * from 宣传员", new Object[] {}), HashMap.class);

        Map model = new HashMap();
        model.put("limkmanList", limkmanList);
        model.put("dateRange",renyuanDateRangeStart.substring(0,8) + "至" + renyuanDateRangeEnd.substring(0,8));
        for (Iterator<HashMap> iterator = limkmanList.iterator(); iterator.hasNext(); ) {
            HashMap linkman =  iterator.next();
            getLinkman(linkman);

            if (StringUtils.equals(linkman.get("name").toString(),"曹建涛")) {
                linkman.put("name","曹桂勇");
            }
            if (StringUtils.equals(linkman.get("name").toString(),"张秀春")) {
                linkman.put("name","岳良全");
            }
        }


        System.out.println(model);









        String excelTemplate = "excelTemplates/联络员/联络员新浆员、超半年、检疫期（献浆后2个月内）.xlsx";
        try(InputStream is = Thread.currentThread().getContextClassLoader().getResourceAsStream(excelTemplate)) {
            if(is==null) {
                throw new PlatformException("模板资源不存在："+excelTemplate);
            }
            File f = new File(root + "\\程序查询\\联络员新浆员、超半年、检疫期（献浆后2个月内）" +  renyuanDateRangeStart.substring(0,8) + "至" + renyuanDateRangeEnd.substring(0,8) + ".xlsx");
            f.getParentFile().mkdirs();
            FileOutputStream os = new FileOutputStream(f);
            JxlsUtils.exportExcel(is,os,model);
        } catch (IOException e) {
            throw new PlatformException(e.getMessage());
        }

    }


    public static Map getLinkman(HashMap linkman) {
        SQLManager sqlManager = ManagerFactory.taibangSqlManager();

        //时间范围内超半年人员
        List<HashMap> chaobannianList = sqlManager.execute(new SQLReady("select count(*) from (\n" +
                "select *,(TO_DAYS(DATE_FORMAT(left(regDate,8),'%Y%m%d'))-TO_DAYS(DATE_FORMAT(left(lastCollectionDate,8),'%Y%m%d'))) as days from (\n" +
                "select donorId as dId, registerDate as regDate,\n" +
                "\t(select c.beginDate from t_bims_plasma_collection as c where c.donorId = dId and c.beginDate < regDate and c.weight > 0 and c.collectionNumberFlag = 'Y' order by c.beginDate desc limit 0,1 ) as lastCollectionDate,\n" +
                "\t(select count(*) from t_bims_plasma_collection as c where c.donorId = dId and c.beginDate between ? and ? and c.weight > 0 order by c.beginDate desc ) as collectionCount\n" +
                " from (\n" +
                "select register.* from \n" +
                "\tt_bims_donor_register as register \n" +
                "\tleft join t_bims_linkman as linkman on register.linkmanId = linkman.linkmanId \n" +
                "\tleft join t_bims_plasma_collection as collection on LEFT(register.recordDate,8) = LEFT(collection.recordDate,8) and register.donorId = collection.donorId\n" +
                "\tleft join t_bims_donor as d on register.donorId = d.donorId\n" +
                " where register.recordDate between ? and ? and linkmanName = ? and collection.weight > 0 and register.changeless = 'N' and\n" +
                "(INSTR(d.remark,'翟毅') = 0 and INSTR(d.remark,'潘颖') = 0 and INSTR(d.remark,'张玲') = 0 and INSTR(d.remark,'隗继云') = 0 and INSTR(d.remark,'高芸') = 0 and INSTR(d.remark,'魏永杰') = 0 and INSTR(d.remark,'焦连云') = 0 and INSTR(d.remark,'韩绍丽') = 0 and INSTR(d.remark,'索建芝') = 0 and INSTR(d.remark,'唐琳') = 0 and INSTR(d.remark,'孟淑文') = 0 and INSTR(d.remark,'郭镇') = 0 and INSTR(d.remark,'张加华') = 0 and INSTR(d.remark,'杨树盈') = 0 and INSTR(d.remark,'王敬君') = 0 and INSTR(d.remark,'姜利') = 0 and INSTR(d.remark,'耿鹏程') = 0 and INSTR(d.remark,'孙莉莉') = 0 and INSTR(d.remark,'李风明') = 0 and INSTR(d.remark,'付佳') = 0 and INSTR(d.remark,'曹孟恺') = 0 and INSTR(d.remark,'刘秀贞') = 0 and INSTR(d.remark,'张淑美') = 0 and INSTR(d.remark,'高谦') = 0 and INSTR(d.remark,'孙爱花') = 0 and INSTR(d.remark,'李晶') = 0 and INSTR(d.remark,'刘合花') = 0 and INSTR(d.remark,'高翠') = 0 and INSTR(d.remark,'周克荣') = 0 and INSTR(d.remark,'胡继雨') = 0 and INSTR(d.remark,'焦莹莹') = 0 and INSTR(d.remark,'吕乃安') = 0 and INSTR(d.remark,'李荣华') = 0 and INSTR(d.remark,'霍文青') = 0 and INSTR(d.remark,'韩萌') = 0 and INSTR(d.remark,'魏学') = 0 and INSTR(d.remark,'石玉') = 0 and INSTR(d.remark,'赵同倩') = 0 and INSTR(d.remark,'崔巍') = 0 and INSTR(d.remark,'朱明玉') = 0 and INSTR(d.remark,'张俊俊') = 0 and INSTR(d.remark,'陈惠芬') = 0 and INSTR(d.remark,'耿延平') = 0 and INSTR(d.remark,'张倩') = 0 and INSTR(d.remark,'胡月慧') = 0 and INSTR(d.remark,'周秀梅') = 0 and INSTR(d.remark,'金洋') = 0 and INSTR(d.remark,'王强') = 0 and INSTR(d.remark,'尚书霞') = 0 and INSTR(d.remark,'牛兆华') = 0 and INSTR(d.remark,'韩昌吉') = 0 and INSTR(d.remark,'赵文超') = 0 and INSTR(d.remark,'韩聪') = 0 and INSTR(d.remark,'李昕') = 0 and INSTR(d.remark,'李睿瑶') = 0 and INSTR(d.remark,'袁甜甜') = 0 and INSTR(d.remark,'李鲲鹏') = 0 and INSTR(d.remark,'康俊') = 0 and INSTR(d.remark,'王心怡') = 0 and INSTR(d.remark,'齐俊鑫') = 0 and INSTR(d.remark,'韩婷婷') = 0 and INSTR(d.remark,'马延鹏') = 0 and INSTR(d.remark,'隗霜') = 0 and INSTR(d.remark,'隗艳') = 0 and INSTR(d.remark,'李梦雨') = 0 and INSTR(d.remark,'燕双') = 0 and INSTR(d.remark,'吕红霞') = 0 and INSTR(d.remark,'时林') = 0 and INSTR(d.remark,'时光') = 0 and INSTR(d.remark,'景晓') = 0 and INSTR(d.remark,'马梦哲') = 0 and INSTR(d.remark,'孙哲') = 0 and INSTR(d.remark,'李晓迪') = 0)\n" +
                "group by d.donorId\n" +
                ") as a\n" +
                ") as b\n" +
                ") as c where days > (180) and collectionCount > 0", new Object[] { renyuanDateRangeStart, renyuanDateRangeNextEnd,renyuanDateRangeStart, renyuanDateRangeEnd,linkman.get("name")}), HashMap.class);
        Long chaobannian = (Long)chaobannianList.get(0).get("count(*)");
        linkman.put("chaobannianTotal",chaobannian);

        //时间范围内超半年人员，2个月内采浆次数大于1次的
        List<HashMap> chaobanniandayu1ciList = sqlManager.execute(new SQLReady("select count(*) from (\n" +
                "select *,(TO_DAYS(DATE_FORMAT(left(regDate,8),'%Y%m%d'))-TO_DAYS(DATE_FORMAT(left(lastCollectionDate,8),'%Y%m%d'))) as days from (\n" +
                "select donorId as dId, registerDate as regDate,\n" +
                "\t(select c.beginDate from t_bims_plasma_collection as c where c.donorId = dId and c.beginDate < regDate and c.weight > 0 and c.collectionNumberFlag = 'Y' order by c.beginDate desc limit 0,1 ) as lastCollectionDate,\n" +
                "\t(select count(*) from t_bims_plasma_collection as c where c.donorId = dId and c.beginDate between ? and ? and c.weight > 0 order by c.beginDate desc ) as collectionCount\n" +
                " from (\n" +
                "select register.* from \n" +
                "\tt_bims_donor_register as register \n" +
                "\tleft join t_bims_linkman as linkman on register.linkmanId = linkman.linkmanId \n" +
                "\tleft join t_bims_plasma_collection as collection on LEFT(register.recordDate,8) = LEFT(collection.recordDate,8) and register.donorId = collection.donorId\n" +
                "\tleft join t_bims_donor as d on register.donorId = d.donorId\n" +
                " where register.recordDate between ? and ? and linkmanName = ? and collection.weight > 0 and register.changeless = 'N' and\n" +
                "(INSTR(d.remark,'翟毅') = 0 and INSTR(d.remark,'潘颖') = 0 and INSTR(d.remark,'张玲') = 0 and INSTR(d.remark,'隗继云') = 0 and INSTR(d.remark,'高芸') = 0 and INSTR(d.remark,'魏永杰') = 0 and INSTR(d.remark,'焦连云') = 0 and INSTR(d.remark,'韩绍丽') = 0 and INSTR(d.remark,'索建芝') = 0 and INSTR(d.remark,'唐琳') = 0 and INSTR(d.remark,'孟淑文') = 0 and INSTR(d.remark,'郭镇') = 0 and INSTR(d.remark,'张加华') = 0 and INSTR(d.remark,'杨树盈') = 0 and INSTR(d.remark,'王敬君') = 0 and INSTR(d.remark,'姜利') = 0 and INSTR(d.remark,'耿鹏程') = 0 and INSTR(d.remark,'孙莉莉') = 0 and INSTR(d.remark,'李风明') = 0 and INSTR(d.remark,'付佳') = 0 and INSTR(d.remark,'曹孟恺') = 0 and INSTR(d.remark,'刘秀贞') = 0 and INSTR(d.remark,'张淑美') = 0 and INSTR(d.remark,'高谦') = 0 and INSTR(d.remark,'孙爱花') = 0 and INSTR(d.remark,'李晶') = 0 and INSTR(d.remark,'刘合花') = 0 and INSTR(d.remark,'高翠') = 0 and INSTR(d.remark,'周克荣') = 0 and INSTR(d.remark,'胡继雨') = 0 and INSTR(d.remark,'焦莹莹') = 0 and INSTR(d.remark,'吕乃安') = 0 and INSTR(d.remark,'李荣华') = 0 and INSTR(d.remark,'霍文青') = 0 and INSTR(d.remark,'韩萌') = 0 and INSTR(d.remark,'魏学') = 0 and INSTR(d.remark,'石玉') = 0 and INSTR(d.remark,'赵同倩') = 0 and INSTR(d.remark,'崔巍') = 0 and INSTR(d.remark,'朱明玉') = 0 and INSTR(d.remark,'张俊俊') = 0 and INSTR(d.remark,'陈惠芬') = 0 and INSTR(d.remark,'耿延平') = 0 and INSTR(d.remark,'张倩') = 0 and INSTR(d.remark,'胡月慧') = 0 and INSTR(d.remark,'周秀梅') = 0 and INSTR(d.remark,'金洋') = 0 and INSTR(d.remark,'王强') = 0 and INSTR(d.remark,'尚书霞') = 0 and INSTR(d.remark,'牛兆华') = 0 and INSTR(d.remark,'韩昌吉') = 0 and INSTR(d.remark,'赵文超') = 0 and INSTR(d.remark,'韩聪') = 0 and INSTR(d.remark,'李昕') = 0 and INSTR(d.remark,'李睿瑶') = 0 and INSTR(d.remark,'袁甜甜') = 0 and INSTR(d.remark,'李鲲鹏') = 0 and INSTR(d.remark,'康俊') = 0 and INSTR(d.remark,'王心怡') = 0 and INSTR(d.remark,'齐俊鑫') = 0 and INSTR(d.remark,'韩婷婷') = 0 and INSTR(d.remark,'马延鹏') = 0 and INSTR(d.remark,'隗霜') = 0 and INSTR(d.remark,'隗艳') = 0 and INSTR(d.remark,'李梦雨') = 0 and INSTR(d.remark,'燕双') = 0 and INSTR(d.remark,'吕红霞') = 0 and INSTR(d.remark,'时林') = 0 and INSTR(d.remark,'时光') = 0 and INSTR(d.remark,'景晓') = 0 and INSTR(d.remark,'马梦哲') = 0 and INSTR(d.remark,'孙哲') = 0 and INSTR(d.remark,'李晓迪') = 0)\n" +
                "group by d.donorId\n" +
                ") as a\n" +
                ") as b\n" +
                ") as c where days > (180) and collectionCount > 2", new Object[] { renyuanDateRangeStart, renyuanDateRangeNextEnd,renyuanDateRangeStart, renyuanDateRangeEnd,linkman.get("name")}), HashMap.class);
        Long chaobanniandayu1ci = (Long)chaobanniandayu1ciList.get(0).get("count(*)");
        linkman.put("chaoCount",chaobanniandayu1ci.intValue());


        //时间范围内检疫期人员，2个月内采浆次数
        List<HashMap> jianyiqiList = sqlManager.execute(new SQLReady("select count(*) from (\n" +
                "\tselect *, (TO_DAYS(DATE_FORMAT(left(rDate,8),'%Y%m%d'))-TO_DAYS(DATE_FORMAT(left(上次献浆时间,8),'%Y%m%d'))) as 献浆间隔 from (\n" +
                "\t\tselect d.donorName,d.donorId as dId, register.recordDate as rDate,\n" +
                "\t\t\t(select c.recordDate from t_bims_plasma_collection as c where c.donorId = dId and c.recordDate < rDate order by c.recordDate desc limit 0,1 ) as 上次献浆时间,\n" +
                "\t\t\t(select count(*) from t_bims_plasma_collection as c where c.donorId = dId and c.beginDate between ? and ? and c.weight > 0 order by c.beginDate desc ) as collectionCount\n" +
                "\t\t from \n" +
                "\t\t\tt_bims_donor_register as register \n" +
                "\t\t\tleft join t_bims_linkman as linkman on register.linkmanId = linkman.linkmanId \n" +
                "\t\t\tleft join t_bims_plasma_collection as collection on LEFT(register.recordDate,8) = LEFT(collection.recordDate,8) and register.donorId = collection.donorId\n" +
                "\t\t\tleft join t_bims_donor as d on register.donorId = d.donorId\n" +
                "\t\t where register.recordDate between ? and ? and linkmanName = ? and collection.weight > 0 and register.changeless != 'N' and\n" +
                "\t\t(INSTR(d.remark,'翟毅') = 0 and INSTR(d.remark,'潘颖') = 0 and INSTR(d.remark,'张玲') = 0 and INSTR(d.remark,'隗继云') = 0 and INSTR(d.remark,'高芸') = 0 and INSTR(d.remark,'魏永杰') = 0 and INSTR(d.remark,'焦连云') = 0 and INSTR(d.remark,'韩绍丽') = 0 and INSTR(d.remark,'索建芝') = 0 and INSTR(d.remark,'唐琳') = 0 and INSTR(d.remark,'孟淑文') = 0 and INSTR(d.remark,'郭镇') = 0 and INSTR(d.remark,'张加华') = 0 and INSTR(d.remark,'杨树盈') = 0 and INSTR(d.remark,'王敬君') = 0 and INSTR(d.remark,'姜利') = 0 and INSTR(d.remark,'耿鹏程') = 0 and INSTR(d.remark,'孙莉莉') = 0 and INSTR(d.remark,'李风明') = 0 and INSTR(d.remark,'付佳') = 0 and INSTR(d.remark,'曹孟恺') = 0 and INSTR(d.remark,'刘秀贞') = 0 and INSTR(d.remark,'张淑美') = 0 and INSTR(d.remark,'高谦') = 0 and INSTR(d.remark,'孙爱花') = 0 and INSTR(d.remark,'李晶') = 0 and INSTR(d.remark,'刘合花') = 0 and INSTR(d.remark,'高翠') = 0 and INSTR(d.remark,'周克荣') = 0 and INSTR(d.remark,'胡继雨') = 0 and INSTR(d.remark,'焦莹莹') = 0 and INSTR(d.remark,'吕乃安') = 0 and INSTR(d.remark,'李荣华') = 0 and INSTR(d.remark,'霍文青') = 0 and INSTR(d.remark,'韩萌') = 0 and INSTR(d.remark,'魏学') = 0 and INSTR(d.remark,'石玉') = 0 and INSTR(d.remark,'赵同倩') = 0 and INSTR(d.remark,'崔巍') = 0 and INSTR(d.remark,'朱明玉') = 0 and INSTR(d.remark,'张俊俊') = 0 and INSTR(d.remark,'陈惠芬') = 0 and INSTR(d.remark,'耿延平') = 0 and INSTR(d.remark,'张倩') = 0 and INSTR(d.remark,'胡月慧') = 0 and INSTR(d.remark,'周秀梅') = 0 and INSTR(d.remark,'金洋') = 0 and INSTR(d.remark,'王强') = 0 and INSTR(d.remark,'尚书霞') = 0 and INSTR(d.remark,'牛兆华') = 0 and INSTR(d.remark,'韩昌吉') = 0 and INSTR(d.remark,'赵文超') = 0 and INSTR(d.remark,'韩聪') = 0 and INSTR(d.remark,'李昕') = 0 and INSTR(d.remark,'李睿瑶') = 0 and INSTR(d.remark,'袁甜甜') = 0 and INSTR(d.remark,'李鲲鹏') = 0 and INSTR(d.remark,'康俊') = 0 and INSTR(d.remark,'王心怡') = 0 and INSTR(d.remark,'齐俊鑫') = 0 and INSTR(d.remark,'韩婷婷') = 0 and INSTR(d.remark,'马延鹏') = 0 and INSTR(d.remark,'隗霜') = 0 and INSTR(d.remark,'隗艳') = 0 and INSTR(d.remark,'李梦雨') = 0 and INSTR(d.remark,'燕双') = 0 and INSTR(d.remark,'吕红霞') = 0 and INSTR(d.remark,'时林') = 0 and INSTR(d.remark,'时光') = 0 and INSTR(d.remark,'景晓') = 0 and INSTR(d.remark,'马梦哲') = 0 and INSTR(d.remark,'孙哲') = 0 and INSTR(d.remark,'李晓迪') = 0)\n" +
                "\t\tgroup by d.donorId\n" +
                "\t) as a \n" +
                ") as b where b.献浆间隔 > (90) and b.collectionCount > 0 order by b.献浆间隔 desc", new Object[] { renyuanDateRangeStart, renyuanDateRangeNextEnd,renyuanDateRangeStart, renyuanDateRangeEnd,linkman.get("name")}), HashMap.class);
        Long jianyiqiCount = (Long)jianyiqiList.get(0).get("count(*)");
        linkman.put("jianyiqiTotal",jianyiqiCount);

        //时间范围内检疫期人员，2个月内采浆次数
        List<HashMap> jianyiqidayu1ciList = sqlManager.execute(new SQLReady("select count(*) from (\n" +
                "\tselect *, (TO_DAYS(DATE_FORMAT(left(rDate,8),'%Y%m%d'))-TO_DAYS(DATE_FORMAT(left(上次献浆时间,8),'%Y%m%d'))) as 献浆间隔 from (\n" +
                "\t\tselect d.donorName,d.donorId as dId, register.recordDate as rDate,\n" +
                "\t\t\t(select c.recordDate from t_bims_plasma_collection as c where c.donorId = dId and c.recordDate < rDate order by c.recordDate desc limit 0,1 ) as 上次献浆时间,\n" +
                "\t\t\t(select count(*) from t_bims_plasma_collection as c where c.donorId = dId and c.beginDate between ? and ? and c.weight > 0 order by c.beginDate desc ) as collectionCount\n" +
                "\t\t from \n" +
                "\t\t\tt_bims_donor_register as register \n" +
                "\t\t\tleft join t_bims_linkman as linkman on register.linkmanId = linkman.linkmanId \n" +
                "\t\t\tleft join t_bims_plasma_collection as collection on LEFT(register.recordDate,8) = LEFT(collection.recordDate,8) and register.donorId = collection.donorId\n" +
                "\t\t\tleft join t_bims_donor as d on register.donorId = d.donorId\n" +
                "\t\t where register.recordDate between ? and ? and linkmanName = ? and collection.weight > 0 and register.changeless != 'N' and\n" +
                "\t\t(INSTR(d.remark,'翟毅') = 0 and INSTR(d.remark,'潘颖') = 0 and INSTR(d.remark,'张玲') = 0 and INSTR(d.remark,'隗继云') = 0 and INSTR(d.remark,'高芸') = 0 and INSTR(d.remark,'魏永杰') = 0 and INSTR(d.remark,'焦连云') = 0 and INSTR(d.remark,'韩绍丽') = 0 and INSTR(d.remark,'索建芝') = 0 and INSTR(d.remark,'唐琳') = 0 and INSTR(d.remark,'孟淑文') = 0 and INSTR(d.remark,'郭镇') = 0 and INSTR(d.remark,'张加华') = 0 and INSTR(d.remark,'杨树盈') = 0 and INSTR(d.remark,'王敬君') = 0 and INSTR(d.remark,'姜利') = 0 and INSTR(d.remark,'耿鹏程') = 0 and INSTR(d.remark,'孙莉莉') = 0 and INSTR(d.remark,'李风明') = 0 and INSTR(d.remark,'付佳') = 0 and INSTR(d.remark,'曹孟恺') = 0 and INSTR(d.remark,'刘秀贞') = 0 and INSTR(d.remark,'张淑美') = 0 and INSTR(d.remark,'高谦') = 0 and INSTR(d.remark,'孙爱花') = 0 and INSTR(d.remark,'李晶') = 0 and INSTR(d.remark,'刘合花') = 0 and INSTR(d.remark,'高翠') = 0 and INSTR(d.remark,'周克荣') = 0 and INSTR(d.remark,'胡继雨') = 0 and INSTR(d.remark,'焦莹莹') = 0 and INSTR(d.remark,'吕乃安') = 0 and INSTR(d.remark,'李荣华') = 0 and INSTR(d.remark,'霍文青') = 0 and INSTR(d.remark,'韩萌') = 0 and INSTR(d.remark,'魏学') = 0 and INSTR(d.remark,'石玉') = 0 and INSTR(d.remark,'赵同倩') = 0 and INSTR(d.remark,'崔巍') = 0 and INSTR(d.remark,'朱明玉') = 0 and INSTR(d.remark,'张俊俊') = 0 and INSTR(d.remark,'陈惠芬') = 0 and INSTR(d.remark,'耿延平') = 0 and INSTR(d.remark,'张倩') = 0 and INSTR(d.remark,'胡月慧') = 0 and INSTR(d.remark,'周秀梅') = 0 and INSTR(d.remark,'金洋') = 0 and INSTR(d.remark,'王强') = 0 and INSTR(d.remark,'尚书霞') = 0 and INSTR(d.remark,'牛兆华') = 0 and INSTR(d.remark,'韩昌吉') = 0 and INSTR(d.remark,'赵文超') = 0 and INSTR(d.remark,'韩聪') = 0 and INSTR(d.remark,'李昕') = 0 and INSTR(d.remark,'李睿瑶') = 0 and INSTR(d.remark,'袁甜甜') = 0 and INSTR(d.remark,'李鲲鹏') = 0 and INSTR(d.remark,'康俊') = 0 and INSTR(d.remark,'王心怡') = 0 and INSTR(d.remark,'齐俊鑫') = 0 and INSTR(d.remark,'韩婷婷') = 0 and INSTR(d.remark,'马延鹏') = 0 and INSTR(d.remark,'隗霜') = 0 and INSTR(d.remark,'隗艳') = 0 and INSTR(d.remark,'李梦雨') = 0 and INSTR(d.remark,'燕双') = 0 and INSTR(d.remark,'吕红霞') = 0 and INSTR(d.remark,'时林') = 0 and INSTR(d.remark,'时光') = 0 and INSTR(d.remark,'景晓') = 0 and INSTR(d.remark,'马梦哲') = 0 and INSTR(d.remark,'孙哲') = 0 and INSTR(d.remark,'李晓迪') = 0)\n" +
                "\t\tgroup by d.donorId\n" +
                "\t) as a \n" +
                ") as b where b.献浆间隔 > (90) and b.collectionCount > 2 order by b.献浆间隔 desc", new Object[] { renyuanDateRangeStart, renyuanDateRangeNextEnd,renyuanDateRangeStart, renyuanDateRangeEnd,linkman.get("name")}), HashMap.class);
        Long jianyiqidayu1ciCount = (Long)jianyiqidayu1ciList.get(0).get("count(*)");
        linkman.put("jianXianCount", jianyiqidayu1ciCount.intValue());


        //时间范围内新浆员，2个月内采浆次数
        List<HashMap> xinList = sqlManager.execute(new SQLReady("select count(*) from (\n" +
                "select register.donorId as dId,\n" +
                "(select count(*) from t_bims_plasma_collection as c where c.donorId = dId and c.beginDate between ? and ? and c.weight > 0 order by c.beginDate desc ) as collectionCount\n" +
                " from \n" +
                "\tt_bims_donor_register as register \n" +
                "\tleft join t_bims_linkman as linkman on register.linkmanId = linkman.linkmanId \n" +
                "\tleft join t_bims_plasma_collection as collection on LEFT(register.recordDate,8) = LEFT(collection.recordDate,8) and register.donorId = collection.donorId\n" +
                "\tleft join t_bims_donor as d on register.donorId = d.donorId\n" +
                " where d.firstCollectDate between ? and ? and linkman.linkmanName = ? and collection.weight > 0 and \n" +
                "(INSTR(d.remark,'翟毅') = 0 and INSTR(d.remark,'潘颖') = 0 and INSTR(d.remark,'张玲') = 0 and INSTR(d.remark,'隗继云') = 0 and INSTR(d.remark,'高芸') = 0 and INSTR(d.remark,'魏永杰') = 0 and INSTR(d.remark,'焦连云') = 0 and INSTR(d.remark,'韩绍丽') = 0 and INSTR(d.remark,'索建芝') = 0 and INSTR(d.remark,'唐琳') = 0 and INSTR(d.remark,'孟淑文') = 0 and INSTR(d.remark,'郭镇') = 0 and INSTR(d.remark,'张加华') = 0 and INSTR(d.remark,'杨树盈') = 0 and INSTR(d.remark,'王敬君') = 0 and INSTR(d.remark,'姜利') = 0 and INSTR(d.remark,'耿鹏程') = 0 and INSTR(d.remark,'孙莉莉') = 0 and INSTR(d.remark,'李风明') = 0 and INSTR(d.remark,'付佳') = 0 and INSTR(d.remark,'曹孟恺') = 0 and INSTR(d.remark,'刘秀贞') = 0 and INSTR(d.remark,'张淑美') = 0 and INSTR(d.remark,'高谦') = 0 and INSTR(d.remark,'孙爱花') = 0 and INSTR(d.remark,'李晶') = 0 and INSTR(d.remark,'刘合花') = 0 and INSTR(d.remark,'高翠') = 0 and INSTR(d.remark,'周克荣') = 0 and INSTR(d.remark,'胡继雨') = 0 and INSTR(d.remark,'焦莹莹') = 0 and INSTR(d.remark,'吕乃安') = 0 and INSTR(d.remark,'李荣华') = 0 and INSTR(d.remark,'霍文青') = 0 and INSTR(d.remark,'韩萌') = 0 and INSTR(d.remark,'魏学') = 0 and INSTR(d.remark,'石玉') = 0 and INSTR(d.remark,'赵同倩') = 0 and INSTR(d.remark,'崔巍') = 0 and INSTR(d.remark,'朱明玉') = 0 and INSTR(d.remark,'张俊俊') = 0 and INSTR(d.remark,'陈惠芬') = 0 and INSTR(d.remark,'耿延平') = 0 and INSTR(d.remark,'张倩') = 0 and INSTR(d.remark,'胡月慧') = 0 and INSTR(d.remark,'周秀梅') = 0 and INSTR(d.remark,'金洋') = 0 and INSTR(d.remark,'王强') = 0 and INSTR(d.remark,'尚书霞') = 0 and INSTR(d.remark,'牛兆华') = 0 and INSTR(d.remark,'韩昌吉') = 0 and INSTR(d.remark,'赵文超') = 0 and INSTR(d.remark,'韩聪') = 0 and INSTR(d.remark,'李昕') = 0 and INSTR(d.remark,'李睿瑶') = 0 and INSTR(d.remark,'袁甜甜') = 0 and INSTR(d.remark,'李鲲鹏') = 0 and INSTR(d.remark,'康俊') = 0 and INSTR(d.remark,'王心怡') = 0 and INSTR(d.remark,'齐俊鑫') = 0 and INSTR(d.remark,'韩婷婷') = 0 and INSTR(d.remark,'马延鹏') = 0 and INSTR(d.remark,'隗霜') = 0 and INSTR(d.remark,'隗艳') = 0 and INSTR(d.remark,'李梦雨') = 0 and INSTR(d.remark,'燕双') = 0 and INSTR(d.remark,'吕红霞') = 0 and INSTR(d.remark,'时林') = 0 and INSTR(d.remark,'时光') = 0 and INSTR(d.remark,'景晓') = 0 and INSTR(d.remark,'马梦哲') = 0 and INSTR(d.remark,'孙哲') = 0 and INSTR(d.remark,'李晓迪') = 0)\n" +
                " group by d.donorId\n" +
                ") as a where a.collectionCount > 0", new Object[] { renyuanDateRangeStart, renyuanDateRangeNextEnd,renyuanDateRangeStart, renyuanDateRangeEnd,linkman.get("name")}), HashMap.class);
        Long xinCount = (Long)xinList.get(0).get("count(*)");
        linkman.put("xinTotal",xinCount.intValue());

        //时间范围内新浆员，2个月内采浆次数
        List<HashMap> xindayu1ciList = sqlManager.execute(new SQLReady("select count(*) from (\n" +
                "select register.donorId as dId,\n" +
                "(select count(*) from t_bims_plasma_collection as c where c.donorId = dId and c.beginDate between ? and ? and c.weight > 0 order by c.beginDate desc ) as collectionCount\n" +
                " from \n" +
                "\tt_bims_donor_register as register \n" +
                "\tleft join t_bims_linkman as linkman on register.linkmanId = linkman.linkmanId \n" +
                "\tleft join t_bims_plasma_collection as collection on LEFT(register.recordDate,8) = LEFT(collection.recordDate,8) and register.donorId = collection.donorId\n" +
                "\tleft join t_bims_donor as d on register.donorId = d.donorId\n" +
                " where d.firstCollectDate between ? and ? and linkman.linkmanName = ? and collection.weight > 0 and \n" +
                "(INSTR(d.remark,'翟毅') = 0 and INSTR(d.remark,'潘颖') = 0 and INSTR(d.remark,'张玲') = 0 and INSTR(d.remark,'隗继云') = 0 and INSTR(d.remark,'高芸') = 0 and INSTR(d.remark,'魏永杰') = 0 and INSTR(d.remark,'焦连云') = 0 and INSTR(d.remark,'韩绍丽') = 0 and INSTR(d.remark,'索建芝') = 0 and INSTR(d.remark,'唐琳') = 0 and INSTR(d.remark,'孟淑文') = 0 and INSTR(d.remark,'郭镇') = 0 and INSTR(d.remark,'张加华') = 0 and INSTR(d.remark,'杨树盈') = 0 and INSTR(d.remark,'王敬君') = 0 and INSTR(d.remark,'姜利') = 0 and INSTR(d.remark,'耿鹏程') = 0 and INSTR(d.remark,'孙莉莉') = 0 and INSTR(d.remark,'李风明') = 0 and INSTR(d.remark,'付佳') = 0 and INSTR(d.remark,'曹孟恺') = 0 and INSTR(d.remark,'刘秀贞') = 0 and INSTR(d.remark,'张淑美') = 0 and INSTR(d.remark,'高谦') = 0 and INSTR(d.remark,'孙爱花') = 0 and INSTR(d.remark,'李晶') = 0 and INSTR(d.remark,'刘合花') = 0 and INSTR(d.remark,'高翠') = 0 and INSTR(d.remark,'周克荣') = 0 and INSTR(d.remark,'胡继雨') = 0 and INSTR(d.remark,'焦莹莹') = 0 and INSTR(d.remark,'吕乃安') = 0 and INSTR(d.remark,'李荣华') = 0 and INSTR(d.remark,'霍文青') = 0 and INSTR(d.remark,'韩萌') = 0 and INSTR(d.remark,'魏学') = 0 and INSTR(d.remark,'石玉') = 0 and INSTR(d.remark,'赵同倩') = 0 and INSTR(d.remark,'崔巍') = 0 and INSTR(d.remark,'朱明玉') = 0 and INSTR(d.remark,'张俊俊') = 0 and INSTR(d.remark,'陈惠芬') = 0 and INSTR(d.remark,'耿延平') = 0 and INSTR(d.remark,'张倩') = 0 and INSTR(d.remark,'胡月慧') = 0 and INSTR(d.remark,'周秀梅') = 0 and INSTR(d.remark,'金洋') = 0 and INSTR(d.remark,'王强') = 0 and INSTR(d.remark,'尚书霞') = 0 and INSTR(d.remark,'牛兆华') = 0 and INSTR(d.remark,'韩昌吉') = 0 and INSTR(d.remark,'赵文超') = 0 and INSTR(d.remark,'韩聪') = 0 and INSTR(d.remark,'李昕') = 0 and INSTR(d.remark,'李睿瑶') = 0 and INSTR(d.remark,'袁甜甜') = 0 and INSTR(d.remark,'李鲲鹏') = 0 and INSTR(d.remark,'康俊') = 0 and INSTR(d.remark,'王心怡') = 0 and INSTR(d.remark,'齐俊鑫') = 0 and INSTR(d.remark,'韩婷婷') = 0 and INSTR(d.remark,'马延鹏') = 0 and INSTR(d.remark,'隗霜') = 0 and INSTR(d.remark,'隗艳') = 0 and INSTR(d.remark,'李梦雨') = 0 and INSTR(d.remark,'燕双') = 0 and INSTR(d.remark,'吕红霞') = 0 and INSTR(d.remark,'时林') = 0 and INSTR(d.remark,'时光') = 0 and INSTR(d.remark,'景晓') = 0 and INSTR(d.remark,'马梦哲') = 0 and INSTR(d.remark,'孙哲') = 0 and INSTR(d.remark,'李晓迪') = 0)\n" +
                " group by d.donorId\n" +
                ") as a where a.collectionCount > 2", new Object[] { renyuanDateRangeStart, renyuanDateRangeNextEnd,renyuanDateRangeStart, renyuanDateRangeEnd,linkman.get("name")}), HashMap.class);
        Long xindayu1ciCount = (Long)xindayu1ciList.get(0).get("count(*)");
        linkman.put("xinCount",xindayu1ciCount.intValue());

        return null;
    }
}
